<?php
/**
 * Created by PhpStorm.
 * User: nightelf
 * Date: 2020/1/13
 * Time: 14:42
 */

namespace App\Http\Controllers\Excel;


use App\Exceptions\ApiException;
use App\Http\Controllers\Controller;
use App\Http\Model\EmployeeModel;
use App\Http\Model\GroupMembersModel;
use App\Http\Model\GroupModel;
use App\Http\Model\HandymanAccountsModel;
use App\Http\Model\MaterialCategoryModel;
use App\Http\Model\MaterialModel;
use App\Http\Model\MaterialSpecModel;
use App\Http\Model\ProjectAreaModel;
use App\Http\Model\ProjectModel;
use App\Http\Model\ProjectRosterModel;
use App\Http\Model\ProjectSectionModel;
use App\Http\Model\WageModel;
use App\Http\Model\WorkmanAccountsModel;
use App\Http\Model\WorkmanAssignmentModel;
use App\Http\Model\YearModel;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Validator;
use Maatwebsite\Excel\Facades\Excel;

class ExportController extends Controller
{
    CONST PATH = '../storage/exports/';
    CONST COLUMN = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
        'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ',];

    /**
     * @param array $data
     * @return string
     */
    private function columns(array $data)
    {
        $num = count($data[0]);
//        if ($num <27){
        $ascii = $num + 64;
        return chr($ascii);
//        }
    }

    /**
     * @param $filename
     * @param $request
     * @return string
     */
    private function downloadURL($filename, $request)
    {
        return '点击<a target="_blank" href="http://' . $_SERVER['HTTP_HOST'] . '/excel/download?name=' . urlencode(str_replace('/','|',$filename)) . '&token=' . $request->input(config('yucheng.token'), '') . '">链接</a>开始下载';
    }

    /**
     * 生成文件名
     * @param $name
     * @return false|string
     */
    private function nameFormat($name)
    {
        return str_replace('/','-',$name);
    }

    /**
     * 生成文件名
     * @param $name
     * @return false|string
     */
    private function fileName($name)
    {
        $name = str_replace('/','|',$name);
        if (in_array(PHP_OS, ['WINNT', 'WIN32', 'Windows'])) {
            $fileName = iconv('utf-8', 'gbk', $name);
        } else {
            $fileName = $name;
        }
        return $fileName;
    }

    /**
     * @param Request $request
     * @return string|\Symfony\Component\HttpFoundation\BinaryFileResponse
     */
    public function download(Request $request)
    {
        $param = $request->only(['name']);
        $filename = $this->fileName(urldecode($param['name']));
        $path = self::PATH . $filename;
        if (file_exists($path)) {
            return response()->download($path, $param['name']);
        } else {
            return "文件不存在,请尝试重新下载";
        }

    }

    /**
     * @param Request $request
     * @return \Illuminate\Http\Response
     * @throws ApiException
     */
    public function employeeLists(Request $request)
    {
        $rules = [
            'projectId' => 'required|integer',
            'professionId' => 'nullable|integer',
            'status' => 'nullable|integer',
        ];
        $message = [
            'projectId.required' => '获取项目参数失败',
            'projectId.integer' => '项目参数类型错误',
            'professionId.integer' => '工种参数类型错误',
            'status.integer' => '工作状态参数类型错误',
        ];
        $param = $request->all();
        $validator = Validator::make($param, $rules, $message);
        if ($validator->passes()) {
            $employeeModel = new EmployeeModel();
            $yearModel = new YearModel();
            $yearInfo = $yearModel->findYear(date('Y-m-d H:i:s'));
            $param['startTime'] = $yearInfo['startTime'];
            $param['endTime'] = $yearInfo['endTime'];
            $data = $employeeModel->lists($param, true);

            $fileName = time() . mt_rand(100, 999);
            $extensions = 'xlsx';
            Excel::create($fileName, function ($excel) use ($data) {
                $excel->sheet('sheet1', function ($sheet) use ($data) {
                    $cellData = [["姓名", "项目", "工种", "工号", "身份证号", "性别", "年龄", "民族", "联系方式", "家庭住址", "银行卡号", "签合同时间", "入场教育时间"]];
                    foreach ($data as $d) {
                        switch ($d->gender) {
                            case 1:
                                $gender = '男';
                                break;
                            case 2:
                                $gender = '女';
                                break;
                            default:
                                $gender = '';
                        }
                        $rowData = [$d->name, $d->projectName, $d->professionName, $d->jobNumber, $d->idcard, $gender, $d->age, $d->nation, $d->phone, $d->homeAddress, $d->bankNumber,  $d->contractTime, $d->eduTime];
                        array_push($cellData, $rowData);

                    }
                    $sheet->fromArray($cellData, null, 'A1', true, false);
                    $count = count($cellData);
                    $column = $this->columns($cellData);
                    $sheet->setColumnFormat(
                        array(
                            'A1:' . $column . $count => '@',
                        )
                    );
                    $sheet->setAutoSize(true);
                });
            })->store($extensions);
            $filename = $fileName . '.' . $extensions;
            $this->msg = $this->downloadURL($filename, $request);
        } else {
            throw new ApiException($validator->errors()->first(), 520101);
        }
        return $this->ajaxResult($this->code, $this->msg, $this->data);
    }

    /**
     * @param Request $request
     * @return \Illuminate\Http\Response
     * @throws \Exception
     */
    public function personalWage(Request $request)
    {
        $rules = [
            'id' => 'required|integer',
        ];
        $message = [
            'id.required' => '获取工人参数失败',
            'id.integer' => '工人参数类型错误',
        ];
        $param = $request->only('id');
        $validator = Validator::make($param, $rules, $message);
        if ($validator->passes()) {
            $employeeModel = new EmployeeModel();
            $wageModel = new WageModel();
            $info = $employeeModel->info($param);
            $wage = $wageModel->personalWage($param);

            $fileName = $info['name'] . '工资列表';
            $fileNameExcel = $this->fileName($fileName);
            $extensions = 'xlsx';
            Excel::create($fileNameExcel, function ($excel) use ($fileName,$info,$wage) {
                $excel->sheet('sheet1', function ($sheet) use ($fileName,$info,$wage) {
                    $sheet->setCellValue('A1', $fileName);
                    $sheet->mergeCells('A1:K1');
                    $cellData = [["月份", "收入汇总", "施工分账", "杂工分账","考勤天数", "考勤工资", "材料费", "借款", "生活费充值","生活费退款",'结余']];

                    $workmanAccounts = 0;
                    $handymanAccounts = 0;
                    $attendance = 0;
                    $materialAccounts = 0;
                    $loan = 0;
                    $livingRecharge = 0;
                    $livingRefund = 0;
                    foreach ($wage as $key => $d) {
                        $income = $d['workmanAccounts'] + $d['handymanAccounts'] + $info['dayValue'] * $d['attendance'];
                        $balance = $income - $d['materialAccounts'] - $d['loan'] - $d['livingRecharge'] + $d['livingRefund'] ;
                        $rowData = [$key, $income, $d['workmanAccounts'], $d['handymanAccounts'],$d['attendance'], $info['dayValue'] * $d['attendance'], $d['materialAccounts'], $d['loan'], $d['livingRecharge'], $d['livingRefund'],$balance];
                        array_push($cellData, $rowData);
                        $workmanAccounts += $d['workmanAccounts'];
                        $handymanAccounts += $d['handymanAccounts'];
                        $attendance += $d['attendance'];
                        $materialAccounts += $d['materialAccounts'];
                        $loan += $d['loan'];
                        $livingRecharge += $d['livingRecharge'];
                        $livingRefund += $d['livingRefund'];
                    }
                    $totalIncome = $workmanAccounts+$handymanAccounts+$attendance*$info['dayValue'];
                    $totalBalance = $totalIncome-$materialAccounts-$loan-$livingRecharge+$livingRefund;
                    array_push($cellData, ['总计',$totalIncome,$workmanAccounts,$handymanAccounts,$attendance,$attendance*$info['dayValue'],$materialAccounts,$loan,$livingRecharge,$livingRefund,$totalBalance]);
                    $sheet->fromArray($cellData, null, 'A2', true, false);
                    $count = count($cellData);
                    $column = $this->columns($cellData);
                    $sheet->setColumnFormat(
                        array(
                            'A1:' . $column . $count => '@',
                        )
                    );
                    $sheet->setWidth([
                        "A" => "12",
                        "B" => "12",
                        "C" => "12",
                        "D" => "12",
                        "E" => "12",
                        "F" => "12",
                        "G" => "12",
                        "H" => "12",
                        "I" => "12",
                        "J" => "12",
                        "K" => "12",
                    ]);
                    $styleArray = array(
                        'borders' => array(
                            'allborders' => array(
                                'style' => \PHPExcel_Style_Border::BORDER_THIN,
                            ),
                        ),
                    );
                    $sheet->getStyle('A1:K'.(count($wage)+3))->applyFromArray($styleArray);
                });
            })->store($extensions);
            $filename = $fileName . '.' . $extensions;
            $this->msg = $this->downloadURL($filename, $request);
        } else {
            throw new ApiException($validator->errors()->first(), 520201);
        }
        return $this->ajaxResult($this->code, $this->msg, $this->data);
    }

    /**
     * 系统设置材料列表导出
     * @param Request $request
     * @return \Illuminate\Http\Response
     */
    public function materialsLists(Request $request){
        $fileName = '材料列表';
        $fileNameExcel = $this->fileName($fileName);
        $extensions = 'xlsx';
        Excel::create($fileNameExcel, function ($excel) {
            $materialCategoryModel = new MaterialCategoryModel();
            $materialModel = new MaterialModel();
            $materialSpecModel = new MaterialSpecModel();
            $categoryLists = $materialCategoryModel->lists();

            foreach ($categoryLists as $key => $c){
                $excel->sheet($c->name, function ($sheet) use($c,$materialModel,$materialSpecModel) {
                    $sheet->setWidth([
                        "A" => "8",
                        "B" => "10",
                        "C" => "15",
                        "D" => "10",
                        "E" => "15",
                    ]);
                    $sheet->setCellValue('A1', $c->name.'材料列表');
                    $sheet->mergeCells('A1:E1');
                    $lists = $materialModel->lists(['categoryId'=>$c->id],true);
                    $line = 3;$t = 1;
                    $cellData = [['编号','物资分类','物资品名','计数单位','规格名称']];
                    foreach ($lists as $k=>$l){$t++;
                        $startLine = $line;
                        $specLists = $materialSpecModel->lists(['materialId'=>$l->id]);
                        if (!empty($specLists)){
                            foreach ($specLists as $k1 => $s){
                                $line++;
                                $unitName = $s->unitName==null?'':$s->unitName;
                                array_push($cellData,[$line-3,$c->name,$l->name,$unitName,$s->spec]);
                            }
                        }else{
                            $line++;
                            array_push($cellData,[$line-3,$c->name,$l->name,'','']);
                        }
                        if (count($specLists) >1){
                            $sheet->mergeCells('B'.$startLine.':B'.($line-1));
                            $sheet->mergeCells('C'.$startLine.':C'.($line-1));
                        }
                    }
                    $sheet->fromArray($cellData, null, 'A2', true, false);
                    $styleArray = array(
                        'borders' => array(
                            'allborders' => array(
                                'style' => \PHPExcel_Style_Border::BORDER_THIN,
                            ),
                        ),
                        'alignment' => array(
                            'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
                            'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
                        )
                    );
                    $sheet->getStyle('A1:E'.($line-1))->applyFromArray($styleArray);
                    $sheet->getDefaultRowDimension()->setRowHeight(30);
                });
            }
        })->store($extensions);
        $filename = $fileName . '.' . $extensions;
        $this->msg = $this->downloadURL($filename, $request);
        return $this->ajaxResult($this->code, $this->msg, $this->data);
    }

    /**
     * @param Request $request
     * @return \Illuminate\Http\Response
     * @throws \Exception
     */
    public function attendance(Request $request){
        $rules = [
            'projectId' => 'required|integer',
            'month' => 'nullable|date_format:Y-m',
        ];
        $message = [
            'projectId.required' => '获取项目参数失败',
            'projectId.integer' => '项目参数类型错误',
            'month.date_format' => '月份参数格式不正确',
        ];
        $input = $request->only(['projectId', 'month']);
        $validator = Validator::make($input, $rules, $message);
        if ($validator->passes()) {
			if ($input['month'] >= date('Y-m')){
				$projectRosterModel = new ProjectRosterModel();
				$attendanceList = $projectRosterModel->attendance($input);
			}else{
				$employeeModel = new EmployeeModel();
				$attendanceList = $employeeModel->attendanceList($input);
			}
            //当月天数

            $projectInfo = (new ProjectModel())->info(['id' => $input['projectId']]);
            $fileName = $projectInfo['name'] . '考勤明细'.date('Y-m-d');
            $fileNameExcel = $this->fileName($fileName);
            $extensions = 'xlsx';
            Excel::create($fileNameExcel, function ($excel) use ($fileName, $projectInfo,$attendanceList,$input) {
                $excel->sheet('sheet1', function ($sheet) use ($fileName, $projectInfo, $attendanceList,$input) {
                    $days = date('t', strtotime($input['month']));
                    $widthLists = [];
                    for ($i =0 ;$i<($days+6);$i++){
                        switch ($i){
                            case 0:
                                $widthLists[self::COLUMN[$i]] = 9;
                                break;
                            case 1:
                                $widthLists[self::COLUMN[$i]] = 12;
                                break;
                            case 2:
                                $widthLists[self::COLUMN[$i]] = 12;
                                break;
                            case 3:
                                $widthLists[self::COLUMN[$i]] = 18;
                                break;
                            case (4+$days):
                                $widthLists[self::COLUMN[$i]] = 8;
                                break;
                            case (5+$days):
                                $widthLists[self::COLUMN[$i]] = 21;
                                break;
                        }
                        if ($i >=4 && $i<(4+$days)){
                            $widthLists[self::COLUMN[$i]] = 6;
                            $sheet->setCellValue(self::COLUMN[$i].'4', ($i-3));
                        }
                    }
                    $sheet->setWidth($widthLists);
                    $sheet->setCellValue('A1', $fileName);
                    $sheet->mergeCells('A1:'.self::COLUMN[($days+5)].'1');

                    $sheet->setCellValue('A2', '项目名称');
                    $sheet->setCellValue('B2', $projectInfo['name']);
                    $sheet->mergeCells('B2:D2');
                    $sheet->setCellValue('E2', '导出时间');
                    $sheet->mergeCells('E2:J2');
                    $sheet->setCellValue('K2', date('Y-m-d H:i:s'));
                    $sheet->mergeCells('K2:'.self::COLUMN[($days+5)].'2');
                    $sheet->setCellValue('A3', '编号');
                    $sheet->mergeCells('A3:A4');
                    $sheet->setCellValue('B3', '姓名');
                    $sheet->mergeCells('B3:B4');
                    $sheet->setCellValue('C3', '工号');
                    $sheet->mergeCells('C3:C4');
                    $sheet->setCellValue('D3', '工种');
                    $sheet->mergeCells('D3:D4');
                    $sheet->setCellValue('E3', '考勤');
                    $sheet->mergeCells('E3:'.self::COLUMN[($days+3)].'3');
                    $sheet->setCellValue(self::COLUMN[($days+4)].'3', '总计');
                    $sheet->mergeCells(self::COLUMN[($days+4)].'3:'.self::COLUMN[($days+4)].'4');
                    $sheet->setCellValue(self::COLUMN[($days+5)].'3', '签字');
                    $sheet->mergeCells(self::COLUMN[($days+5)].'3:'.self::COLUMN[($days+5)].'4');

                    $cellData = [];
                    $num = 0;
                    $totalCount = 0;
                    $everydayTotal = [];
                    foreach ($attendanceList as $d) {
                        $num++;
                        $rowData = [$num,$d->name,$d->jobNumber,$d->professionName];
                        $count = 0;
                        foreach ($d->attendance as $key => $value) {
//                            if ($input['projectId'] == $value['projectId']){
                                $rowData[] = $value['long'];
                                $count += $value['long'];
                                if (!isset($everydayTotal[$key])){
                                    $everydayTotal[$key] = 0;
                                }
                                $everydayTotal[$key] +=$value['long'];
//                            }else{
//                                $rowData[] = '';
//                            }
                        }
                        $totalCount+=$count;
                        $rowData[] = $count;
                        $rowData[] = '';
                        array_push($cellData, $rowData);
                    }
                    $sheet->mergeCells('A'.(count($attendanceList)+5).':D'.(count($attendanceList)+5));
                    $sheet->setCellValue('A'.(count($attendanceList)+5), '总计');
                    foreach ($everydayTotal as $k=> $edt){
                        $sheet->setCellValue(self::COLUMN[($k+3)].(count($attendanceList)+5), $edt);
                    }
                    $sheet->setCellValue(self::COLUMN[($days+4)].(count($attendanceList)+5), $totalCount);
                    $sheet->fromArray($cellData, null, 'A5', true, false);

                    $styleArray = array(
                        'borders' => array(
                            'allborders' => array(
                                'style' => \PHPExcel_Style_Border::BORDER_THIN,
                            ),
                        ),
                        'alignment' => array(
                            'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
                            'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                        ),
                        'font' => array(
                            'size' => 14,
                        ),
                    );
                    $sheet->getStyle('A1:'.self::COLUMN[($days+5)].(count($attendanceList)+5))->applyFromArray($styleArray);
                    $height = $sheet->getHighestRow();
                    for ($i=1;$i<=$height;$i++){
                        $sheet->getRowDimension($i)->setRowHeight(45);
                    }
                });
            })->store($extensions);
            $filename = $fileName . '.' . $extensions;
            $this->msg = $this->downloadURL($filename, $request);
        } else {
            throw new ApiException($validator->errors()->first(), 520301);
        }
        return $this->ajaxResult($this->code, $this->msg, $this->data);
    }


    /**
     * @param Request $request
     * @return \Illuminate\Http\Response
     * @throws \Exception
     */
    public function attendanceAll(Request $request){
        $rules = [
            'projectId' => 'required|integer',
            'month' => 'nullable|date_format:Y-m',
        ];
        $message = [
            'projectId.required' => '获取项目参数失败',
            'projectId.integer' => '项目参数类型错误',
            'month.date_format' => '月份参数格式不正确',
        ];
        $input = $request->only(['projectId', 'month']);
        $validator = Validator::make($input, $rules, $message);
        if ($validator->passes()) {
            $employeeModel = new EmployeeModel();
            $attendanceList = $employeeModel->attendanceList($input);
            //当月天数
            $fileName = '考勤明细'.date('Y-m-d');
            $fileNameExcel = $this->fileName($fileName);
            $extensions = 'xlsx';
            Excel::create($fileNameExcel, function ($excel) use ($fileName,$attendanceList) {
                $excel->sheet('sheet1', function ($sheet) use ($fileName, $attendanceList) {
                    $days = date('t');
                    $widthLists = [];
                    for ($i =0 ;$i<($days+7);$i++){
                        switch ($i){
                            case 0:
                                $widthLists[self::COLUMN[$i]] = 8;
                                break;
                            case 1:
                                $widthLists[self::COLUMN[$i]] = 22;
                                break;
                            case 2:
                                $widthLists[self::COLUMN[$i]] = 12;
                                break;
                            case 3:
                                $widthLists[self::COLUMN[$i]] = 12;
                                break;
                            case 4:
                                $widthLists[self::COLUMN[$i]] = 18;
                                break;
                            case (5+$days):
                                $widthLists[self::COLUMN[$i]] = 8;
                                break;
                            case (6+$days):
                                $widthLists[self::COLUMN[$i]] = 21;
                                break;
                        }
                        if ($i >=5 && $i<(5+$days)){
                            $widthLists[self::COLUMN[$i]] = 6;
                            $sheet->setCellValue(self::COLUMN[$i].'4', ($i-4));
                        }
                    }
                    $sheet->setWidth($widthLists);
                    $sheet->setCellValue('A1', $fileName);
                    $sheet->mergeCells('A1:'.self::COLUMN[($days+6)].'1');

                    $sheet->setCellValue('A2', '导出时间');
                    $sheet->mergeCells('A2:C2');
                    $sheet->setCellValue('D2', date('Y-m-d H:i:s'));
                    $sheet->mergeCells('D2:'.self::COLUMN[($days+6)].'2');
                    $sheet->setCellValue('A3', '编号');
                    $sheet->mergeCells('A3:A4');
                    $sheet->setCellValue('B3', '项目');
                    $sheet->mergeCells('B3:B4');
                    $sheet->setCellValue('C3', '姓名');
                    $sheet->mergeCells('C3:C4');
                    $sheet->setCellValue('D3', '工号');
                    $sheet->mergeCells('D3:D4');
                    $sheet->setCellValue('E3', '工种');
                    $sheet->mergeCells('E3:E4');
                    $sheet->setCellValue('F3', '考勤');
                    $sheet->mergeCells('F3:'.self::COLUMN[($days+4)].'3');
                    $sheet->setCellValue(self::COLUMN[($days+5)].'3', '总计');
                    $sheet->mergeCells(self::COLUMN[($days+5)].'3:'.self::COLUMN[($days+5)].'4');
                    $sheet->setCellValue(self::COLUMN[($days+6)].'3', '签字');
                    $sheet->mergeCells(self::COLUMN[($days+6)].'3:'.self::COLUMN[($days+6)].'4');

                    $cellData = [];
                    $num = 0;
                    $totalCount = 0;
                    $everydayTotal = [];
                    foreach ($attendanceList as $d) {
                        $num++;
                        $rowData = [$num,$d->projectName,$d->name,$d->jobNumber,$d->professionName];
                        $count = 0;
                        foreach ($d->attendance as $key => $value) {
                            $rowData[] = $value['long'];
                            $count += $value['long'];
                            if (!isset($everydayTotal[$key])){
                                $everydayTotal[$key] = 0;
                            }
                            $everydayTotal[$key] +=$value['long'];
                        }
                        $totalCount+=$count;
                        $rowData[] = $count;
                        $rowData[] = '';
                        array_push($cellData, $rowData);
                    }
                    $sheet->mergeCells('A'.(count($attendanceList)+5).':E'.(count($attendanceList)+5));
                    $sheet->setCellValue('A'.(count($attendanceList)+5), '总计');
                    foreach ($everydayTotal as $k=> $edt){
                        $sheet->setCellValue(self::COLUMN[($k+4)].(count($attendanceList)+5), $edt);
                    }
                    $sheet->setCellValue(self::COLUMN[($days+5)].(count($attendanceList)+5), $totalCount);
                    $sheet->fromArray($cellData, null, 'A5', true, false);

                    $styleArray = array(
                        'borders' => array(
                            'allborders' => array(
                                'style' => \PHPExcel_Style_Border::BORDER_THIN,
                            ),
                        ),
                        'alignment' => array(
                            'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
                            'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                        ),
                        'font' => array(
                            'size' => 14,
                        ),
                    );
                    $sheet->getStyle('A1:'.self::COLUMN[($days+6)].(count($attendanceList)+5))->applyFromArray($styleArray);
                    $height = $sheet->getHighestRow();
                    for ($i=1;$i<=$height;$i++){
                        $sheet->getRowDimension($i)->setRowHeight(45);
                    }
                });
            })->store($extensions);
            $filename = $fileName . '.' . $extensions;
            $this->msg = $this->downloadURL($filename, $request);
        } else {
            throw new ApiException($validator->errors()->first(), 520401);
        }
        return $this->ajaxResult($this->code, $this->msg, $this->data);
    }

    /**
     * @param Request $request
     * @return \Illuminate\Http\Response
     * @throws ApiException
     */
    public function workmanAssignment(Request $request)
    {
        $rules = [
            'projectId' => 'required|integer',
            'sectionId' => 'required|integer',
            'professionId' => 'required|integer',
            'groupId' => 'required|integer',
            'batch' => 'nullable|integer',
        ];
        $message = [
            'projectId.required' => '获取项目参数失败',
            'projectId.integer' => '项目参数类型错误',
            'sectionId.required' => '获取施工区参数失败',
            'sectionId.integer' => '施工区参数类型错误',
            'professionId.required' => '获取工种参数失败',
            'professionId.integer' => '工种参数类型错误',
            'groupId.required' => '获取班组参数失败',
            'groupId.integer' => '班组参数类型错误',
            'batch.integer' => '批次参数类型错误',
        ];
        $input = $request->only(['projectId', 'sectionId', 'professionId', 'groupId','batch']);
        $validator = Validator::make($input, $rules, $message);
        if ($validator->passes()) {
            $workmanAssignmentModel = new WorkmanAssignmentModel();
//            $workmanAccountsModel = new WorkmanAccountsModel();
            $data = [
                'projectId' => $input['projectId'],
                'sectionId' => $input['sectionId'],
                'groupId' => $input['groupId'],
                'batch' => $input['batch'],
            ];
            //施工项分账记录
            $assignment = $workmanAssignmentModel->lists($data);
            //班组成员分账记录
//            $separate = $workmanAccountsModel->lists($data);

            $projectInfo = (new ProjectModel())->info(['id' => $input['projectId']]);
            $sectionInfo = (new ProjectSectionModel())->info(['id' => $input['sectionId']]);
            $areaInfo = (new ProjectAreaModel())->info(['id' => $sectionInfo['areaId']]);
            $groupInfo = (new GroupModel())->info(['id' => $input['groupId']]);
            $projectGroupMembersModel = new GroupMembersModel();
            $memberLists = $projectGroupMembersModel->lists(['groupId' => $input['groupId']]);
            $fileName =$this->nameFormat($projectInfo['name'] . '-' . $areaInfo['name'] . '-' . $sectionInfo['name'] . '-' . $groupInfo['name'] . '分账表');
            $fileNameExcel = $this->fileName($fileName);
            $extensions = 'xlsx';
            Excel::create($fileNameExcel, function ($excel) use ($assignment, $fileName, $projectInfo, $sectionInfo, $areaInfo, $groupInfo, $memberLists) {
                $excel->sheet('sheet1', function ($sheet) use ($assignment, $fileName, $projectInfo, $sectionInfo, $areaInfo, $groupInfo, $memberLists) {
                    $sheet->setWidth([
                        "A" => "9",
                        "B" => "15",
                        "C" => "15",
                        "D" => "15",
                        "E" => "12",
                        "F" => "12",
                        "G" => "12",
                        "H" => "40",
                        "I" => "12",
                        "J" => "18",
                    ]);
                    $sheet->setCellValue('A1', $fileName);
                    $sheet->mergeCells('A1:J1');
                    $sheet->setCellValue('A2', '项目');
                    $sheet->setCellValue('B2', $projectInfo['name']);
                    $sheet->setCellValue('C2', '楼栋/施工区');
                    $sheet->setCellValue('D2', $areaInfo['name']);
                    $sheet->setCellValue('E2', '楼层/施工段');
                    $sheet->setCellValue('F2', $sectionInfo['name']);
                    $sheet->setCellValue('G2', '班组');
                    $sheet->setCellValue('H2', $groupInfo['name']);

                    $cellData = [["编号", "施工项","分类", "工程量", "单位", "单价", "总价（元）", "备注", "记录时间", "签字"]];
                    $totalPrice = 0;
                    $label = [];
                    foreach ($assignment as $key => $d) {
                        $rowData = [$key + 1, $d->assignmentName,$d->labelName, $d->amount, $d->unitName, $d->price, $d->totalPrice, $d->remark, substr($d->completeTime,0,10), ''];
                        array_push($cellData, $rowData);
                        $totalPrice += $d->totalPrice;
                        if ($d->labelId != 0){
                            if(!isset($label[$d->labelName])){
                                $label[$d->labelName] = 0;
                            }
                            $label[$d->labelName]+= $d->totalPrice;
                        }
                    }
                    array_push($cellData, ['总计', '', '', '', '','', $totalPrice, '', '', ''],['', '', '', '', '', '', '', '', '',''], ['', '','', '', '', '', '', '', '', '']);
                    array_push($cellData, ['编号', '班组成员', '分账金额', '备注','','','','','', '签字']);
                    foreach ($memberLists as $k => $m) {
                        array_push($cellData, [$k + 1, $m->employeeName,'','','','','', '', '', '']);
                    }
                    $sheet->fromArray($cellData, null, 'A3', true, false);

                    $labelStr = '施工项分类汇总：  ';
                    foreach ($label as $k => $l){
                        $labelStr.= $k. '：'. $l . '元      ';
                    }
                    $sheet->setCellValue('A'.(count($assignment)+5), $labelStr);
                    $sheet->mergeCells('A'.(count($assignment)+5).':J'.(count($assignment)+5));

                    $styleArray = array(
                        'borders' => array(
                            'allborders' => array(
                                'style' => \PHPExcel_Style_Border::BORDER_THIN,
                            ),
                        ),
                        'alignment' => array(
                            'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
                            'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
                        ),
//                        'font'  => array(
//                            'size'  => 14,
//                        )
                    );
                    $sheet->getStyle('A1:J'.(count($assignment)+5))->applyFromArray($styleArray);
                    $sheet->getStyle('A'.(count($assignment)+7).':J'.(count($assignment)+7+count($memberLists)))->applyFromArray($styleArray);
                    $height = $sheet->getHighestRow();
                    for ($i=1;$i<=$height;$i++){
                        $sheet->getRowDimension($i)->setRowHeight(45);
                        if ($i >= (7+count($assignment))){
                            $sheet->mergeCells('D'.$i.':I'.$i);
                        }
                    }
                    $sheet->getStyle("A2:J2")->getAlignment()->setWrapText(TRUE);
                    $sheet->getStyle("B4:B".(count($assignment)+3))->getAlignment()->setWrapText(TRUE);
                    $sheet->getStyle("H4:H".(count($assignment)+3))->getAlignment()->setWrapText(TRUE);

                });
            })->store($extensions);
            $filename = $fileName . '.' . $extensions;
            $this->msg = $this->downloadURL($filename, $request);
        } else {
            throw new ApiException($validator->errors()->first(), 520501);
        }
        return $this->ajaxResult($this->code, $this->msg, $this->data);
    }

    /**
     * @param Request $request
     * @return \Illuminate\Http\Response
     * @throws ApiException
     */
    public function workmanAccounts(Request $request)
    {
        $rules = [
            'projectId' => 'required|integer',
            'sectionId' => 'required|integer',
            'professionId' => 'required|integer',
            'groupId' => 'required|integer',
            'batch' => 'nullable|integer',
        ];
        $message = [
            'projectId.required' => '获取项目参数失败',
            'projectId.integer' => '项目参数类型错误',
            'sectionId.required' => '获取施工区参数失败',
            'sectionId.integer' => '施工区参数类型错误',
            'professionId.required' => '获取工种参数失败',
            'professionId.integer' => '工种参数类型错误',
            'groupId.required' => '获取班组参数失败',
            'groupId.integer' => '班组参数类型错误',
            'batch.integer' => '批次参数类型错误',
        ];
        $param = $request->only(['projectId', 'sectionId', 'professionId', 'groupId','batch']);
        $validator = Validator::make($param, $rules, $message);
        if ($validator->passes()) {
            $workmanAccountsModel = new WorkmanAccountsModel();
//            $data = [
//                'projectId' => $input['projectId'],
//                'sectionId' => $input['sectionId'],
//                'groupId' => $input['groupId'],
//            ];
            //班组成员分账记录
            $separate = $workmanAccountsModel->export($param);

            $projectInfo = (new ProjectModel())->info(['id' => $param['projectId']]);
            $sectionInfo = (new ProjectSectionModel())->info(['id' => $param['sectionId']]);
            $areaInfo = (new ProjectAreaModel())->info(['id' => $sectionInfo['areaId']]);
            $groupInfo = (new GroupModel())->info(['id' => $param['groupId']]);
            $fileName = $projectInfo['name'] . '-' . $areaInfo['name'] . '-' . $sectionInfo['name'] . '-' . $groupInfo['name'] . '班组成员分账表';
            $fileNameExcel = $this->fileName($fileName);
            $extensions = 'xlsx';
            Excel::create($fileNameExcel, function ($excel) use ($separate, $fileName, $projectInfo, $sectionInfo, $areaInfo, $groupInfo) {
                $excel->sheet('sheet1', function ($sheet) use ($separate, $fileName, $projectInfo, $sectionInfo, $areaInfo, $groupInfo) {
                    $sheet->setWidth([
                        "A" => "10",
                        "B" => "15",
                        "C" => "15",
                        "D" => "15",
                        "E" => "15",
                        "F" => "15",
                        "G" => "40",
                        "H" => "25",
                    ]);
                    $sheet->setCellValue('A1', $fileName);
                    $sheet->mergeCells('A1:H1');
                    $sheet->setCellValue('A2', '项目');
                    $sheet->setCellValue('B2', $projectInfo['name']);
                    $sheet->setCellValue('C2', '楼栋/施工区');
                    $sheet->setCellValue('D2', $areaInfo['name']);
                    $sheet->setCellValue('E2', '楼层/施工段');
                    $sheet->setCellValue('F2', $sectionInfo['name']);
                    $sheet->setCellValue('G2', '班组');
                    $sheet->setCellValue('H2', $groupInfo['name']);

                    $cellData_1 = [["编号", "姓名", "工号", "角色", "分账金额", "记录时间", "备注", "签字"]];
                    $totalPrice = 0;
                    foreach ($separate as $key => $d) {
                        $leader = $d->isLeader ? '组长' : '组员';
//                        $rowData = [$key + 1, $d->employeeName, $d->jobNumber, $leader, $d->account, $d->separateTime, $d->remark, ''];
                        $rowData = [$key + 1, $d->employeeName, $d->jobNumber, $leader, $d->account, '', '', ''];
                        array_push($cellData_1, $rowData);
                        $totalPrice += $d->account;
                    }
                    array_push($cellData_1, ['总计', '', '', '', $totalPrice, '', '', '']);
                    $sheet->fromArray($cellData_1, null, 'A3', true, false);

                    $styleArray = array(
                        'borders' => array(
                            'allborders' => array(
                                'style' => \PHPExcel_Style_Border::BORDER_THIN,
                            ),
                        ),
                        'alignment' => array(
                            'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
                            'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
                        ),
                    );
                    $sheet->getStyle('A1:H'.(count($separate)+4))->applyFromArray($styleArray);
                    $height = $sheet->getHighestRow();
                    for ($i=1;$i<=$height;$i++){
                        $sheet->getRowDimension($i)->setRowHeight(45);
                    }
                    $sheet->getStyle("A2:H2")->getAlignment()->setWrapText(TRUE);
                });
            })->store($extensions);
            $filename = $fileName . '.' . $extensions;
            $this->msg = $this->downloadURL($filename, $request);
        } else {
            throw new ApiException($validator->errors()->first(), 520601);
        }
        return $this->ajaxResult($this->code, $this->msg, $this->data);
    }

    /**
     * @param Request $request
     * @return \Illuminate\Http\Response
     * @throws ApiException
     */
    public function handymanAccounts(Request $request)
    {
        $rules = [
            'projectId' => 'required|integer',
            'startTime' => 'nullable|date_format:Y-m-d',
            'endTime' => 'nullable|date_format:Y-m-d',
        ];
        $message = [
            'projectId.required' => '获取项目参数失败',
            'projectId.integer' => '项目参数类型不正确',
            'startTime.date_format' => '日期格式不正确',
            'endTime.date_format' => '日期格式不正确',
        ];
        $input = $request->only(['projectId', 'startTime', 'endTime','search']);
        $validator = Validator::make($input, $rules, $message);
        if ($validator->passes()) {
            $handymanAccountsModel = new HandymanAccountsModel();
            $handymanLists = $handymanAccountsModel->getHandyman($input);
//            if (empty($handymanLists)){
//                throw new ApiException('没有符合条件的数据可以导出', 520701);
//            }

            $projectInfo = (new ProjectModel())->info(['id' => $input['projectId']]);
            $fileName = $projectInfo['name'] . '杂工包工明细账表';
            $fileNameExcel = $this->fileName($fileName);
            $extensions = 'xlsx';
            Excel::create($fileNameExcel, function ($excel) use ($fileName, $projectInfo,$input,$handymanLists,$handymanAccountsModel) {
                $employeeModel = new EmployeeModel();
                foreach ($handymanLists as $handyman){
                    $input['employeeId'] = $handyman->employeeId;
                    $employeeInfo = $employeeModel->info(['id' => $input['employeeId']]);
                    $data = $handymanAccountsModel->export($input);
                    $excel->sheet($employeeInfo['name'], function ($sheet) use ($fileName, $projectInfo, $employeeInfo, $data) {
                        $sheet->setWidth([
                            "A" => "6",
                            "B" => "12",
                            "C" => "9",
                            "D" => "12",
                            "E" => "12",
                            "F" => "70",
                            "G" => "15",
                        ]);
                        $sheet->setCellValue('A1', $fileName);
                        $sheet->mergeCells('A1:G1');
                        $sheet->setCellValue('A2', '项目');
                        $sheet->setCellValue('B2', $projectInfo['name']);
                        $sheet->mergeCells('B2:D2');
                        $sheet->setCellValue('E2', '导出时间');
                        $sheet->setCellValue('F2', date('Y-m-d H:i:s'));
                        $sheet->mergeCells('F2:G2');


                        $cellData_1 = [["编号", "姓名", "工号", "作业时间", "分账金额", "施工内容", "签字"]];
                        $totalPrice = 0;
                        foreach ($data as $key => $d) {
                            $rowData = [$key + 1, $d->name, $d->jobNumber, $d->separateTime, $d->accounts, $d->work, ''];
                            array_push($cellData_1, $rowData);
                            $totalPrice += $d->accounts;
                        }
                        array_push($cellData_1, ['总计', '', '', '', $totalPrice, '', '', '']);
                        $sheet->fromArray($cellData_1, null, 'A3', true, false);

                        $styleArray = array(
                            'borders' => array(
                                'allborders' => array(
                                    'style' => \PHPExcel_Style_Border::BORDER_THIN,
                                ),
                            ),
                            'alignment' => array(
                                'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
                                'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                            ),
                        );
                        $sheet->getStyle('A1:E'.(count($data)+4))->applyFromArray($styleArray);
                        $sheet->getStyle('G1:G'.(count($data)+4))->applyFromArray($styleArray);
                        $styleArray2 = array(
                            'borders' => array(
                                'allborders' => array(
                                    'style' => \PHPExcel_Style_Border::BORDER_THIN,
                                ),
                            ),
                            'alignment' => array(
                                'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
                                'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
                            ),
                        );
                        $sheet->getStyle('F1:F'.(count($data)+4))->applyFromArray($styleArray2);
                        $height = $sheet->getHighestRow();
                        for ($i=1;$i<=$height;$i++){
                            $sheet->getRowDimension($i)->setRowHeight(45);
                        }
                        $sheet->getStyle("F4:F".(count($data)+3))->getAlignment()->setWrapText(TRUE);
                    });
                }
            })->store($extensions);
            $filename = $fileName . '.' . $extensions;
            $this->msg = $this->downloadURL($filename, $request);
        } else {
            throw new ApiException($validator->errors()->first(), 520701);
        }
        return $this->ajaxResult($this->code, $this->msg, $this->data);
    }

    /**
     * @param Request $request
     * @return \Illuminate\Http\Response
     * @throws ApiException
     * @throws \Exception
     */
    public function employeeWageLists(Request $request){
        $rules = [
            'projectId' => 'required|integer',
            'professionId' => 'nullable|integer',
            'startTime' => 'nullable|date_format:Y-m-d',
            'endTime' => 'nullable|date_format:Y-m-d',
        ];
        $message = [
            'projectId.required' => '获取项目参数失败',
            'projectId.integer' => '项目参数类型错误',
            'professionId.integer' => '工种参数类型错误',
            'startTime.date_format' => '起始时间格式错误',
            'endTime.date_format' => '截止时间格式错误',
        ];
        $param = $request->only(['projectId','professionId','startTime','endTime','search']);
        $validator = Validator::make($param, $rules, $message);
        if ($validator->passes()) {
            $employeeModel = new EmployeeModel();
            $startTime = $param['startTime'];
            $endTime = $param['endTime'];

            //确定当前工程年度时间，搜索当前工程年度项目中的所有工人
            $yearModel = new YearModel();
            $yearInfo = $yearModel->findYear(date('Y-m-d'));
            $param['startTime'] = $yearInfo['startTime'];
            $param['endTime'] = $yearInfo['endTime'];
            $param['isManager'] = 0;
            $lists = $employeeModel->lists($param,true);

            //按照时间区间汇总工资
            $startTime = $startTime===''?$yearInfo['startTime']:$startTime;
            $endTime = $endTime===''?$yearInfo['endTime']:$endTime;

            foreach ($lists as $key => $l) {
                $wages = $employeeModel->wages($l->id,$startTime,$endTime);
                $lists[$key]->wage = $wages;
            }

            $projectInfo = (new ProjectModel())->info(['id' => $param['projectId']]);
            $name = isset($projectInfo['name'])?$projectInfo['name']:'';
            $fileName = $name . '工人工资汇总表'.date('Y-m-d');
            $fileNameExcel = $this->fileName($fileName);
            $extensions = 'xlsx';
            Excel::create($fileNameExcel, function ($excel) use ($fileName, $name,$param,$lists) {
                $excel->sheet('汇总', function ($sheet) use ($fileName, $name, $param, $lists) {
                    $sheet->setWidth([
                        "A" => "12",
                        "B" => "12",
                        "C" => "12",
                        "D" => "12",
                        "E" => "12",
                        "F" => "12",
                        "G" => "12",
                        "H" => "12",
                        "I" => "12",
                        "J" => "12",
                        "K" => "12",
                        "L" => "12",
                        "M" => "12",
                        "N" => "12",
                    ]);
                    $sheet->setCellValue('A1', $fileName);
                    $sheet->mergeCells('A1:N1');
                    $sheet->setCellValue('A2', '项目');
                    $sheet->setCellValue('B2', $name);
                    $sheet->mergeCells('B2:C2');
                    $sheet->setCellValue('D2', '汇总周期');
                    $sheet->setCellValue('E2', $param['startTime']);
                    $sheet->mergeCells('E2:F2');
                    $sheet->setCellValue('G2', $param['endTime']);
                    $sheet->mergeCells('G2:H2');
                    $sheet->setCellValue('I2', '导出时间');
                    $sheet->setCellValue('J2', date('Y-m-d H:i:s'));
                    $sheet->mergeCells('J2:K2');


                    $cellData = [["编号", "姓名", "工号", "在职状态", "结余", "收入汇总", "施工分账", "杂工分账", "考勤工资", "材料费", "借款", "生活费充值", "生活费退款"]];
                    $workmanAccountsSum = 0;
                    $handymanAccountsSum = 0;
                    $attendanceSum = 0;
                    $materialOrderSum = 0;
                    $loanSum = 0;
                    $livingRechargeSum = 0;
                    $livingRefundSum = 0;
                    foreach ($lists as $key => $d) {
                        $workmanAccounts = 0;
                        $handymanAccounts = 0;
                        $attendance = 0;
                        $materialOrder = 0;
                        $loan = 0;
                        $livingRecharge = 0;
                        $livingRefund = 0;
                        foreach ($d->wage as $w){
                            $workmanAccounts += $w['workmanAccounts'];
                            $handymanAccounts += $w['handymanAccounts'];
                            $attendance += $w['attendance'];
                            $materialOrder += $w['materialOrder'];
                            $loan += $w['loan'];
                            $livingRecharge += $w['livingRecharge'];
                            $livingRefund += $w['livingRefund'];
                        }
                        $income = $workmanAccounts + $handymanAccounts + $attendance*$d->dayValue;
                        switch ($d->status){
                            case 1:
                                $status = '';
                                break;
                            case 2:
                                $status = '';
                                break;
                            case 3:
                                $status = '';
                                break;
                            case 4:
                                $status = '';
                                break;
                            default:
                                $status = '';
                                break;
                        }
                        $rowData = [
                            $key + 1,
                            $d->name,
                            $d->jobNumber,
                            $status,
                            $income-$materialOrder-$loan-$livingRecharge+$livingRefund,
                            $income,
                            $workmanAccounts,
                            $handymanAccounts,
                            $attendance*$d->dayValue,
                            $materialOrder,
                            $loan,
                            $livingRecharge,
                            $livingRefund
                        ];
                        array_push($cellData,$rowData);
                        $workmanAccountsSum += $workmanAccounts;
                        $handymanAccountsSum += $handymanAccounts;
                        $attendanceSum += $attendance*$d->dayValue;
                        $materialOrderSum += $materialOrder;
                        $loanSum += $loan;
                        $livingRechargeSum += $livingRecharge;
                        $livingRefundSum += $livingRefund;
                    }
                    array_push($cellData,['','','','','','','','','','','','','','',]);
                    $sheet->fromArray($cellData, null, 'A3', true, false);
                    $sheet->setCellValue('A'.(count($lists)+4), '总计：');
                    $sheet->setCellValue('E'.(count($lists)+4), $workmanAccountsSum+$handymanAccountsSum+$attendanceSum-$materialOrderSum-$loanSum-$livingRechargeSum+$livingRefundSum);
                    $sheet->setCellValue('F'.(count($lists)+4), $workmanAccountsSum+$handymanAccountsSum+$attendanceSum);
                    $sheet->setCellValue('G'.(count($lists)+4), $workmanAccountsSum);
                    $sheet->setCellValue('H'.(count($lists)+4), $handymanAccountsSum);
                    $sheet->setCellValue('I'.(count($lists)+4), $attendanceSum);
                    $sheet->setCellValue('J'.(count($lists)+4), $materialOrderSum);
                    $sheet->setCellValue('K'.(count($lists)+4), $loanSum);
                    $sheet->setCellValue('L'.(count($lists)+4), $livingRechargeSum);
                    $sheet->setCellValue('M'.(count($lists)+4), $livingRefundSum);

                    $styleArray = array(
                        'borders' => array(
                            'allborders' => array(
                                'style' => \PHPExcel_Style_Border::BORDER_THIN,
                            ),
                        ),
                    );
                    $sheet->getStyle('A1:N'.(count($lists)+4))->applyFromArray($styleArray);
                });
            })->store($extensions);
            $filename = $fileName . '.' . $extensions;
            $this->msg = $this->downloadURL($filename, $request);
        } else {
            throw new ApiException($validator->errors()->first(), 520801);
        }
        return $this->ajaxResult($this->code, $this->msg, $this->data);
    }

    /**
     * @param Request $request
     * @return \Illuminate\Http\Response
     * @throws ApiException
     * @throws \Exception
     */
    public function managerWageLists(Request $request){
        $rules = [
            'projectId' => 'required|integer',
            'professionId' => 'nullable|integer',
            'startTime' => 'nullable|date_format:Y-m-d',
            'endTime' => 'nullable|date_format:Y-m-d',
        ];
        $message = [
            'projectId.required' => '获取项目参数失败',
            'projectId.integer' => '项目参数类型错误',
            'professionId.integer' => '工种参数类型错误',
            'startTime.date_format' => '起始时间格式错误',
            'endTime.date_format' => '截止时间格式错误',
        ];
        $param = $request->only(['projectId','professionId','startTime','endTime','search']);
        $validator = Validator::make($param, $rules, $message);
        if ($validator->passes()) {
            $employeeModel = new EmployeeModel();
            $startTime = $param['startTime'];
            $endTime = $param['endTime'];

            //确定当前工程年度时间，搜索当前工程年度项目中的所有工人
            $yearModel = new YearModel();
            $yearInfo = $yearModel->findYear(date('Y-m-d'));
            $param['startTime'] = $yearInfo['startTime'];
            $param['endTime'] = $yearInfo['endTime'];
            $param['isManager'] = 1;
            $lists = $employeeModel->lists($param,true);

            //按照时间区间汇总工资
            $startTime = $startTime===''?$yearInfo['startTime']:$startTime;
            $endTime = $endTime===''?$yearInfo['endTime']:$endTime;

            foreach ($lists as $key => $l) {
                $wages = $employeeModel->wages($l->id,$startTime,$endTime);
                $lists[$key]->wage = $wages;
            }

            $projectInfo = (new ProjectModel())->info(['id' => $param['projectId']]);
            $name = isset($projectInfo['name'])?$projectInfo['name']:'';
            $fileName = $name . '管理人员工资汇总表'.date('Y-m-d');
            $fileNameExcel = $this->fileName($fileName);
            $extensions = 'xlsx';
            Excel::create($fileNameExcel, function ($excel) use ($fileName, $name,$param,$lists) {
                $excel->sheet('汇总', function ($sheet) use ($fileName, $name, $param, $lists) {
                    $sheet->setWidth([
                        "A" => "12",
                        "B" => "12",
                        "C" => "12",
                        "D" => "12",
                        "E" => "12",
                        "F" => "12",
                        "G" => "12",
                        "H" => "12",
                        "I" => "12",
                        "J" => "12",
                        "K" => "12",
                        "L" => "12",
                        "M" => "12",
                        "N" => "12",
                    ]);
                    $sheet->setCellValue('A1', $fileName);
                    $sheet->mergeCells('A1:N1');
                    $sheet->setCellValue('A2', '项目');
                    $sheet->setCellValue('B2', $name);
                    $sheet->mergeCells('B2:C2');
                    $sheet->setCellValue('D2', '汇总周期');
                    $sheet->setCellValue('E2', $param['startTime']);
                    $sheet->mergeCells('E2:F2');
                    $sheet->setCellValue('G2', $param['endTime']);
                    $sheet->mergeCells('G2:H2');
                    $sheet->setCellValue('I2', '导出时间');
                    $sheet->setCellValue('J2', date('Y-m-d H:i:s'));
                    $sheet->mergeCells('J2:K2');


                    $cellData = [["编号", "姓名", "工号", "在职状态", "结余", "收入汇总", "施工分账", "杂工分账", "考勤工资", "材料费", "借款", "生活费充值", "生活费退款"]];
                    $workmanAccountsSum = 0;
                    $handymanAccountsSum = 0;
                    $attendanceSum = 0;
                    $materialOrderSum = 0;
                    $loanSum = 0;
                    $livingRechargeSum = 0;
                    $livingRefundSum = 0;
                    foreach ($lists as $key => $d) {
                        $workmanAccounts = 0;
                        $handymanAccounts = 0;
                        $attendance = 0;
                        $materialOrder = 0;
                        $loan = 0;
                        $livingRecharge = 0;
                        $livingRefund = 0;
                        foreach ($d->wage as $w){
                            $workmanAccounts += $w['workmanAccounts'];
                            $handymanAccounts += $w['handymanAccounts'];
                            $attendance += $w['attendance'];
                            $materialOrder += $w['materialOrder'];
                            $loan += $w['loan'];
                            $livingRecharge += $w['livingRecharge'];
                            $livingRefund += $w['livingRefund'];
                        }
                        $income = $workmanAccounts + $handymanAccounts + $attendance*$d->dayValue;
                        switch ($d->status){
                            case 1:
                                $status = '';
                                break;
                            case 2:
                                $status = '';
                                break;
                            case 3:
                                $status = '';
                                break;
                            case 4:
                                $status = '';
                                break;
                            default:
                                $status = '';
                                break;
                        }
                        $rowData = [
                            $key + 1,
                            $d->name,
                            $d->jobNumber,
                            $status,
                            $income-$materialOrder-$loan-$livingRecharge+$livingRefund,
                            $income,
                            $workmanAccounts,
                            $handymanAccounts,
                            $attendance*$d->dayValue,
                            $materialOrder,
                            $loan,
                            $livingRecharge,
                            $livingRefund
                        ];
                        array_push($cellData,$rowData);
                        $workmanAccountsSum += $workmanAccounts;
                        $handymanAccountsSum += $handymanAccounts;
                        $attendanceSum += $attendance*$d->dayValue;
                        $materialOrderSum += $materialOrder;
                        $loanSum += $loan;
                        $livingRechargeSum += $livingRecharge;
                        $livingRefundSum += $livingRefund;
                    }
                    array_push($cellData,['','','','','','','','','','','','','','',]);
                    $sheet->fromArray($cellData, null, 'A3', true, false);
                    $sheet->setCellValue('A'.(count($lists)+4), '总计：');
                    $sheet->setCellValue('E'.(count($lists)+4), $workmanAccountsSum+$handymanAccountsSum+$attendanceSum-$materialOrderSum-$loanSum-$livingRechargeSum+$livingRefundSum);
                    $sheet->setCellValue('F'.(count($lists)+4), $workmanAccountsSum+$handymanAccountsSum+$attendanceSum);
                    $sheet->setCellValue('G'.(count($lists)+4), $workmanAccountsSum);
                    $sheet->setCellValue('H'.(count($lists)+4), $handymanAccountsSum);
                    $sheet->setCellValue('I'.(count($lists)+4), $attendanceSum);
                    $sheet->setCellValue('J'.(count($lists)+4), $materialOrderSum);
                    $sheet->setCellValue('K'.(count($lists)+4), $loanSum);
                    $sheet->setCellValue('L'.(count($lists)+4), $livingRechargeSum);
                    $sheet->setCellValue('M'.(count($lists)+4), $livingRefundSum);

                    $styleArray = array(
                        'borders' => array(
                            'allborders' => array(
                                'style' => \PHPExcel_Style_Border::BORDER_THIN,
                            ),
                        ),
                    );
                    $sheet->getStyle('A1:N'.(count($lists)+4))->applyFromArray($styleArray);
                });
            })->store($extensions);
            $filename = $fileName . '.' . $extensions;
            $this->msg = $this->downloadURL($filename, $request);
        } else {
            throw new ApiException($validator->errors()->first(), 520801);
        }
        return $this->ajaxResult($this->code, $this->msg, $this->data);
    }
}